package net.atomarrow.db.executor;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException;

import net.atomarrow.annotation.Inject;
import net.atomarrow.controllers.converter.TypeConverter;
import net.atomarrow.db.parser.batch.BatchField;
import net.atomarrow.db.parser.batch.BatchSetter;
import net.atomarrow.db.parser.batch.BatchSqlHolder;
import net.atomarrow.ioc.ActionContext;

public class JdbcExecutor {

    @Inject
    private TypeConverter typeConverter;
    
    public boolean batchUpdate(BatchSqlHolder sqlHolder) {
        Connection conn = ActionContext.getContext().getConnection();
        if(sqlHolder.isMultiValues()){
           return mulityValuesInsert(sqlHolder, conn);
        }else{
           return normalBatchInsert(sqlHolder, conn);
        }
    }

    private boolean normalBatchInsert(BatchSqlHolder sqlHolder, Connection conn) {
        PreparedStatement ps = null;
        try {
            boolean justRight=false;
            ps = conn.prepareStatement(sqlHolder.getSql());
            List<BatchSetter> batchSetters = sqlHolder.getBatchSetters();
            int i=0;
            for(BatchSetter setter:batchSetters){
                justRight=false;
                int j=1;
                while (setter.hasNext()) {
                    BatchField field = setter.next();
                    ps.setObject(j, field.getValue(), field.getType());
                    j++;
                }
                ps.addBatch();
                if (i % 500 == 0) {
                    justRight=true;
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            if(!justRight){
                ps.executeBatch();
                ps.clearBatch();
            }
            return true;
        } catch (MySQLTransactionRollbackException e) {
        	//回滚时遇到死锁
        	return normalBatchInsert(sqlHolder, conn);
		} catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
            return false;
        }finally{
            close(ps);
        }
    }


    private boolean mulityValuesInsert(BatchSqlHolder sqlHolder, Connection conn) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            List<String> sqls = sqlHolder.getSqls();
            for(String sql:sqls){
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            stmt.clearBatch();
            return true;
        } catch (MySQLTransactionRollbackException e) {
        	//回滚时遇到死锁
        	return mulityValuesInsert(sqlHolder, conn);
		} catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
            return false;
        } finally {
            close(stmt);
        }
    }


    private void rollBack(Exception e) {
        ActionContext.getContext().setNeedRollback(true);
        ActionContext.getContext().setServiceException(e);
    }

    public List<Map<String, Object>> queryForList(String sql) {
        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery(sql);
            ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            while (resultSet.next()) {
                list.add(getResultMap(resultSet));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt, resultSet);
        }
        return Collections.EMPTY_LIST;
    }

    public Map<String, Object> queryForMap(String sql) {

        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                return getResultMap(resultSet);
            }
            return Collections.EMPTY_MAP;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(stmt, resultSet);
        }
        return Collections.EMPTY_MAP;
    }

    private Map<String, Object> getResultMap(ResultSet resultSet)
            throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            map.put(rsmd.getColumnLabel(i), resultSet.getObject(i));
        }
        return map;
    }

    public Integer queryForInt(String sql) {
        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt, resultSet);
        }
        return 0;
    }

    public <T> List<T> queryForList(String sql, Class<T> clazz) {
        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery(sql);
            ArrayList<T> list = new ArrayList<T>();
            while (resultSet.next()) {
                list.add(typeConverter.converter(new Object[]{resultSet.getObject(1)}, clazz));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt, resultSet);
        }
        return Collections.EMPTY_LIST;
    }


    public int update(String sql) {
        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            System.out.println(sql);
            return stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt);
        }
        return 0;
    }

    public <T> T queryForObject(String sql, Class<T> clazz) {

        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                return typeConverter.converter(new Object[]{resultSet.getObject(1)}, clazz);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt, resultSet);
        }
        return null;
    }

    public Serializable add(String sql, String primaryKey) {

        Connection conn = ActionContext.getContext().getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = conn.prepareStatement(sql, new String[] { primaryKey });
            ps.executeUpdate();
            resultSet = ps.getGeneratedKeys();
            if (resultSet != null && resultSet.next()) {
                return (Serializable) resultSet.getObject(1);// 返回主键值
            }
        } catch (MySQLTransactionRollbackException e) {
        	//回滚时遇到死锁
        	return add(sql, primaryKey);
		} catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(ps, resultSet);
        }
        return null;
    }


    public int update(String sql, Object[] params) {
        Connection conn = ActionContext.getContext().getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            int i = 0;
            for (Object object : params) {
                ps.setObject(++i, object);
            }
            return ps.executeUpdate();
        } catch (MySQLTransactionRollbackException e) {
        	//回滚时遇到死锁
        	return update(sql, params);
		} catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(ps);
        }
        return 0;
    }

    public void execute(String sql) {
        Connection conn = ActionContext.getContext().getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (MySQLTransactionRollbackException e) {
        	//回滚时遇到死锁
        	execute(sql);
		} catch (SQLException e) {
            e.printStackTrace();
            rollBack(e);
        } finally {
            close(stmt);
        }
    }

    private void close(PreparedStatement ps) {
        try {
            if(ps!=null&&!ps.isClosed()){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private void close(Statement stmt, ResultSet resultSet) {
        try {
            if (resultSet != null && !resultSet.isClosed()) {
                resultSet.close();
            }
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private void close(Statement stmt) {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private void close(PreparedStatement ps, ResultSet resultSet) {
        try {
            if (resultSet != null && !resultSet.isClosed()) {
                resultSet.close();
            }
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
